import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

my_course_router = APIRouter(prefix="/school/tsCourse", tags=["课程表管理"], dependencies=[Depends(oauth2_scheme)])


class CourseScheduleSearch(PaginationBase):
    # 筛选条件
    fcourseno: Optional[str] = Field("", description="课程编号")
    fcoursename: Optional[str] = Field("", description="课程名称(支持前缀匹配)")


@my_course_router.get("/list", summary="课程表查询接口")
def list_my_courses(search, db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from a_ts_course where 1=1"
    if search.fcourseno != "":
        sql = sql + " and fcourse_no = :fcourseno "
        param["fcourseno"] = search.fcourseno
    if search.fcoursename != "":
        sql = sql + " and fcourse_name like concat('%', :fcoursename ,'%')"
        param["fcoursename"] = search.fcoursename
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fcourse_no,fcourse_name,fnote,fstate,fupddate,foperator"
    sql = sql + " from a_ts_course where 1=1"
    if search.fcourseno != "":
        sql = sql + " and fcourse_no = :fcourseno "
        param["fcourseno"] = search.fcourseno
    if search.fcoursename != "":
        sql = sql + " and fcourse_name like concat('%', :fcoursename ,'%')"
        param["fcoursename"] = search.fcoursename
    if search.order == "desc":
        sql = sql + " order by  fcourse_no desc "
    else:
        sql = sql + " order by  fcourse_no asc "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = search.limit
    param["offset"] = search.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fcourseno'] = data[key][0]
        d1['fcoursename'] = data[key][1]
        d1['fnote'] = data[key][2]
        d1['fstate'] = data[key][3]
        d1['fupddate'] = data[key][4]
        d1['foperator'] = data[key][5]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class CourseScheduleCreate(BaseModel):
    """课程表资料新增请求参数"""
    fcourseno: str = Field(..., description="课程编号")
    fcoursename: str = Field(..., description="课程名称")
    fstate: int = Field(..., description="课程状态")
    fupddate: str = Field(..., description="更新日期(格式需与数据库存储一致)")
    fnote: Optional[str] = Field("", description="课程备注，默认为空")


@my_course_router.post("/create", summary="课程表资料新增接口")
def create_my_course(create: CourseScheduleCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select 1 from a_ts_course where fcourse_no= :fcourseno "
            data = db.execute(text(sql), {"fcourseno": create.fcourseno}).fetchall()
            if len(data) > 0:
                return BaseResponse.error(message="课程表已存在")
            else:
                sql = "insert into a_ts_course(fcourse_no,fcourse_name,fnote,fstate,fupddate,foperator)"
                sql = sql + " values( :fcourseno , :fcoursename , :fnote , :fstate ,now(), :userid );"
                db.execute(text(sql), {
                    "fcourseno": create.fcourseno,
                    "fcoursename": create.fcoursename,
                    "fnote": create.fnote,
                    "fstate": create.fstate,
                    "userid": user_id,
                })
                data = {"fcourseno": create.fcourseno
                    , "fcoursename": create.fcoursename
                    , "fnote": create.fnote
                    , "fstate": create.fstate
                    , "fupddate": create.fupddate
                    , "foperator": user_id
                        }
                return BaseResponse.success(data=data)

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@my_course_router.post("/update", summary="课程表修改接口")
def update_my_course(update: CourseScheduleCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update a_ts_course"
            sql = sql + " set fcourse_name= :fcoursename ,fnote= :fnote "
            sql = sql + ",fstate= :fstate ,fupddate=now(),foperator= :userid "
            sql = sql + " where fcourse_no= :fcourseno "
            db.execute(text(sql), {
                "fcoursename": update.fcoursename,
                "fnote": update.fnote,
                "fstate": update.fstate,
                "userid": user_id,
                "fcourseno": update.fcourseno
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@my_course_router.post("/delete/{fcourseno}", summary="课程表删除接口")
def delete_my_course(fcourseno: str = Path(description="课程表id"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "delete from a_ts_course where fcourse_no= :fcourseno "
            db.execute(text(sql), {
                "fcourseno": fcourseno
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
